package se.study.jdbc;

import java.sql.*;

/**
 * @Author: tiger
 * @Date:2018/5/23
 * @Description: JDBC测试
 */
public class JdbcTest {
    public static void main(String[] args) {
        Connection connect = getConnect();
        selectTest(connect);
    }

    /**
     *
     */
    private static void selectTest(Connection connect) {
        String findSql = "SELECT * FROM `user`";
        try {
            // 3.获取PreparedStatement对象
            PreparedStatement preState = connect.prepareStatement(findSql);
            // 4.执行SQL
            ResultSet resultSet = preState.executeQuery();
            // 5.处理ResultSet结果集
            // 得到列数
            int columnCount = resultSet.getMetaData().getColumnCount();
            System.out.println("columnCount:" + columnCount);
            while (resultSet.next()) {
                String id = resultSet.getString(1);
                String username = resultSet.getString(2);
                String age = resultSet.getString(3);
                String address = resultSet.getString(4);
                System.out.println("id = " + id + ",\tusername = " + username + ",\tage = " + age + ",\taddress = " + address);
            }
            System.out.println("---------------------------------------------");
            resultSet.beforeFirst();
            while (resultSet.next()) {
                System.out.println("id = " + resultSet.getInt("ID") + ",\tusername = " + resultSet.getString("USER_NAME") + ",\tage = " + resultSet.getInt("AGE") + ",\taddress = " + resultSet.getString("ADDRESS"));
            }
            // 6.关闭资源
            resultSet.close();
            preState.close();
            connect.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void delTest(Connection connect) {
        String delSql = "DELETE FROM `user` WHERE id = ?";
        try {
            // 3.获取PreparedStatement对象
            PreparedStatement preState = connect.prepareStatement(delSql);
            preState.setInt(1, 6);
            // 4.执行SQL
            int execute = preState.executeUpdate();
            // 5.处理ResultSet结果集
            System.out.println("执行结果：" + execute);
            // 6.关闭资源
            preState.close();
            connect.close();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

        }
    }

    private static void insertTest(Connection connect) {
        String insertSql = "INSERT INTO `user` (USER_NAME, AGE, ADDRESS) VALUES (?,?,?)";
        try {
            // 3.获取PreparedStatement对象
            PreparedStatement preState = connect.prepareStatement(insertSql);
            preState.setString(1, "保罗");
            preState.setInt(2, 32);
            preState.setString(3, "休斯顿-火箭");
            // 4.执行SQL
            int i = preState.executeUpdate();
            // 5.处理ResultSet结果集
            System.out.println("执行结果：" + i);
            // 6.关闭资源
            preState.close();
            connect.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    private static Connection getConnect() {
        // JDBC连接Mysql5 com.mysql.jdbc.Driver
        // String driver = "com.mysql.jdbc.Driver";
        // JDBC连接Mysql6 com.mysql.cj.jdbc.Driver  需要指定时区serverTimezone: serverTimezone=UTC，会比中国时间早8个小时，如果在中国，可以选择Asia/Shanghai或者Asia/Hongkong
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = null;
        try {
            // 1.classLoader,加载对应驱动
            Class.forName(driver);
            // 2.获取Connection对象
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}
